﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;

namespace DAL.Service.Impl
{
   public class UserClassServiceImpl : BaseServiceImpl,IUserClassService
    {

        public IList getUserClasslist(string className, string teacher, string in_s, string in_e, int pageno, int pagesize)
        {

            MyQuery query = new MyQuery();
            StringBuilder sb = new StringBuilder();
            string _sql = " where uic_state =  " + (int)DAL.Enums.classState.可用;
            if (!String.IsNullOrEmpty(className))
            {
                _sql += " and uic_class like '%" + className + "%'";
            }
            if (!String.IsNullOrEmpty(teacher))
            {
                _sql += " and uic_teacher like '%" + teacher + "%'";
            }
            if (!String.IsNullOrEmpty(in_s))
            {
                _sql += " and uic_date >= @in_s";
                query.addParas("@in_s", in_s);
            }
            if (!String.IsNullOrEmpty(in_e))
            {
                in_e = in_e + " 23:59:59";
                _sql += " and uic_date < @in_e";
                query.addParas("@in_e", in_e);
            }
            if (pageno == 1)
            {
                sb.Append("SELECT count(uic_id) as total FROM joy.UserClass " + _sql + ";");
                sb.Append("select top " + pagesize + " *,isnull(uic_teacher,'无班主任') ct  FROM joy.UserClass " + _sql + " order by uic_id desc;");
            }
            else
            {
                sb.Append("select * from(");
                sb.Append("select *,isnull(uic_teacher,'无班主任') ct,ROW_NUMBER() OVER (order by uic_id desc) as rno FROM joy.UserClass " + _sql);
                sb.Append(") as a where a.rno>@start and a.rno<@end  order by uic_id desc;");
                query.PageNo = pageno;
                query.PageSize = pagesize; ;
            }
            query.setQueryString(sb.ToString());
            return base.getList(query);
        }


        public IList getUserClassbyid(int id)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("SELECT * FROM joy.UserClass WHERE uic_id=@id");
            query.addParas("@id", id);
            return base.getList(query);
        }



        public int deleteUserClass(int id)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("UPDATE joy.UserClass SET uic_state=@state WHERE uic_id=@id");
            query.addParas("@state", (int)DAL.Enums.classState.不可用);
            query.addParas("@id", id);
            return base.exeUpdateSql(query);
        }
        public IList getDdlClass()
        { 
            MyQuery query = new MyQuery();
            query.setQueryString("select uic_id,uic_class,uic_class+'/'+isnull(uic_teacher,'无班主任') ct from joy.UserClass where uic_state=1 order by uic_id desc");
            return base.getList(query);
        }


        public int getCountByClassName(string className, int id)
        {
            MyQuery query = new MyQuery();
            query.setQueryString(" select count(1) as total from joy.UserClass where uic_class='" + className + "'" + " and uic_id != " + id);
            Hashtable ht = (Hashtable)this.getList(query)[0];
            return Convert.ToInt32(ht["total"]);
        }
    }
}
